﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;
using System.IO;
using NPOI.HPSF;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Web;

namespace HHMMS.Core.Common
{
    /// <summary>
    /// 提供将DataTable数据源导出到Excel的方法。
    /// </summary>
    public class XlsWriter
    {
        #region 字段属性
        private Dictionary<string, string> headers = new Dictionary<string, string>();
        private HSSFWorkbook workbook = null;//工作簿对象
        private Sheet sheet = null;//模板工作表
        private Regex localRegex = new Regex(@"([A-Z]{0,2})(\d{1,5})", RegexOptions.IgnoreCase);

        /// <summary>
        /// 获取或待生成Excel的数据源DataTable对象。
        /// </summary>
        public DataTable DataSource
        {
            get;
            set;
        }

        /// <summary>
        /// 获取或设置第一个工作表的名称。
        /// </summary>
        public string SheetName
        {
            get { return workbook.GetSheetName(0); }
            set { workbook.SetSheetName(0, value); }
        }
        #endregion

        #region 构造方法
        /// <summary>
        /// 初始化TemplateToExcel类的新实例。
        /// </summary>
        public XlsWriter()
        {
            this.InitializeWorkbook();
        }

        /// <summary>
        /// 初始化TemplateToExcel类的新实例并指定Excel数据源。
        /// </summary>
        public XlsWriter(DataTable dataSource)
            : this()
        {
            this.DataSource = dataSource;
        }
        #endregion

        #region 私有方法
        /// <summary>
        /// 初始化工作簿对象。
        /// </summary>
        protected void InitializeWorkbook()
        {
            workbook = new HSSFWorkbook();


            //create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "进销存管理系统";
            workbook.DocumentSummaryInformation = dsi;

            //create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = DateTime.Now.ToShortDateString();
            si.Author = HttpContext.Current.User.Identity.Name;
            si.CreateDateTime = DateTime.Now;
            workbook.SummaryInformation = si;

            //创建一个新的工作表
            sheet = workbook.CreateSheet();
        }

        /// <summary>
        /// 根据单元格的位置字符串返回行列索引。
        /// </summary>
        /// <param name="cellLocation"></param>
        /// <param name="rowIndex"></param>
        /// <param name="columnIndex"></param>
        private void GetCellLocation(string cellLocation, out int rowIndex, out int columnIndex)
        {
            Match match = localRegex.Match(cellLocation);
            if (match == null || !match.Success)
                throw new Exception(string.Format("单元格位置字符串\"{0}\"格式不正确。", cellLocation));

            //列号
            string str = null;
            str = match.Groups[1].Value.ToUpper();
            columnIndex = str.Length == 2 ? (((int)str[0] - 64) * 26 + ((int)str[1] - 64)) : ((int)str[0] - 64);
            if (columnIndex > 256)
                throw new Exception(string.Format("单元格位置的列超过最大范围256。", cellLocation));

            //行号
            rowIndex = int.Parse(match.Groups[2].Value);
            if(rowIndex > 65536)
                throw new Exception(string.Format("单元格位置的行超过最大范围65536。", cellLocation));

            rowIndex = rowIndex - 1;
            columnIndex = columnIndex - 1;
        }
        #endregion

        #region 公开方法
        /// <summary>
        /// 设置指定位置的单元格的值，该位置是由单元格所在位置的行列索引组成。
        /// </summary>
        /// <param name="rowIndex">单元格所在的行索引</param>
        /// <param name="columnIndex">单元格所在的列索引</param>
        /// <param name="value">单元格显示的值</param>
        public Cell SetCellValue(int rowIndex, int columnIndex, object value)
        {
            Row dr = sheet.GetRow(rowIndex);
            if (dr == null)
                dr = sheet.CreateRow(rowIndex);

            Cell cell = dr.GetCell(columnIndex);
            if (cell == null)
                cell = dr.CreateCell(columnIndex);

            if (value != null && value != DBNull.Value)
            {
                if (value.GetType() == typeof(bool))
                    cell.SetCellValue(Convert.ToBoolean(value));
                else if (value.GetType() == typeof(double)
                    || value.GetType() == typeof(float)
                    || value.GetType() == typeof(int)
                    || value.GetType() == typeof(decimal))
                    cell.SetCellValue(Convert.ToDouble(value));
                else
                    cell.SetCellValue(Convert.ToString(value));
            }
            return cell;
        }

        /// <summary>
        /// 设置指定位置的单元格的值，该位置是Excel中表示位置的字符串如"B2"。
        /// </summary>
        /// <param name="cellLocation"></param>
        /// <param name="value"></param>
        public Cell SetCellValue(string cellLocation, object value)
        {
            int rowIndex, columnIndex;
            this.GetCellLocation(cellLocation, out rowIndex, out columnIndex);
            if (rowIndex > -1 && columnIndex > -1)
            {
                return this.SetCellValue(rowIndex, columnIndex, value);
            }
            return null;
        }

        /// <summary>
        /// 设置指定列的宽度，为-1时根据内容自适应，为0时则隐藏该列。
        /// </summary>
        /// <param name="columnIndex"></param>
        /// <param name="width"></param>
        public void SetColumnWidth(int columnIndex, int width)
        {
            if (sheet != null)
            {
                if (width == -1)
                    sheet.AutoSizeColumn(columnIndex);
                else if (width > 0)
                    sheet.SetColumnWidth(columnIndex, width);
            }
        }

        /// <summary>
        /// 设置指定的列是否可见。
        /// </summary>
        /// <param name="columnIndex"></param>
        /// <param name="visible"></param>
        public void SetColumnVisible(int columnIndex, bool visible)
        {
            sheet.SetColumnHidden(columnIndex, !visible);
        }

        public void SetActiveCell(int rowIndex, int columnIndex)
        {
            sheet.SetActiveCell(rowIndex, columnIndex);
        }

        /// <summary>
        /// 获取一个新的单元格样式实例。
        /// </summary>
        public CellStyle NewCellStyle()
        {
            if (workbook != null)
                return workbook.CreateCellStyle();
            return null;
        }

        /// <summary>
        /// 为数据源设置表头。
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="headerText"></param>
        public void SetHeaders(string columnName, string headerText)
        {
            if (!this.headers.ContainsKey(columnName))
            {
                headers.Add(columnName, headerText);
            }
            else
            {
                headers[columnName] = headerText;
            }
        }

        /// <summary>
        /// 从Excel中指定索引的行开始填充数据到当前Excel中。
        /// </summary>
        /// <param name="formatFunc">格式化函数，第一个单元格所在列的名称，第二个参数为单元格格式化前值，返回格式化后的值</param>
        public void Fill(Func<string,object,object> formatFunc = null)
        {
            if (this.DataSource == null)
                return;

            int columnIndex = 0;
            if (headers != null && headers.Count > 0)
            {
                //设置了表头则以设置的表头为基准生成Excel
                foreach (var item in headers)
                {
                    var cell = this.SetCellValue(0, columnIndex, item.Value);
                    var style = this.NewCellStyle();
                    style.Alignment = HorizontalAlignment.CENTER;
                    style.FillPattern = FillPatternType.SOLID_FOREGROUND;
                    style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
                    cell.CellStyle = style;
                    this.SetColumnWidth(columnIndex, 25*256);
                    columnIndex++;
                }
            }
            else if (this.DataSource != null)
            {
                //未设置表头则以DataTable表架构来生成
                foreach (DataColumn column in this.DataSource.Columns)
                {
                    var cell = this.SetCellValue(0, columnIndex, column.ColumnName);
                    var style = this.NewCellStyle();
                    style.Alignment = HorizontalAlignment.CENTER;
                    style.FillPattern = FillPatternType.SOLID_FOREGROUND;
                    style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
                    cell.CellStyle = style;
                    this.SetColumnWidth(columnIndex, 25 * 256);
                    columnIndex++;
                }
            }

            //填充内容
            int currentRowIndex = 1;
            columnIndex = 0;
            foreach (DataRow dr in this.DataSource.Rows)
            {
                columnIndex = 0;
                if (headers != null && headers.Count > 0)
                {
                    //设置了表头则以设置的表头为基准生成Excel
                    foreach (var item in headers)
                    {
                        object val = dr[item.Key];
                        if (formatFunc != null)
                            val = formatFunc(item.Key, val);
                        this.SetCellValue(currentRowIndex, columnIndex, val);
                        columnIndex++;
                    }
                }
                else
                {
                    //未设置表头则以DataTable表架构来生成
                    foreach (DataColumn column in this.DataSource.Columns)
                    {
                        object val = dr[column.ColumnName];
                        if (formatFunc != null)
                            val = formatFunc(column.ColumnName, val);
                        this.SetCellValue(currentRowIndex, columnIndex, val);
                        columnIndex++;
                    }
                }
                currentRowIndex++;
            }
            sheet.ForceFormulaRecalculation = true;
        }

        /// <summary>
        /// 获取生成excel内存流。
        /// </summary>
        public MemoryStream OutPutStream
        {
            get
            {
                MemoryStream stream = new MemoryStream();
                workbook.Write(stream);
                return stream;
            }
        }

        /// <summary>
        /// 保存当前输出到.xls文件。
        /// </summary>
        /// <param name="fileName"></param>
        public void SaveAsXls(string fileName)
        {
            if (HttpContext.Current != null)
            {
                using (MemoryStream stream = this.OutPutStream)
                {
                    HttpContext.Current.Response.Clear();
                    HttpContext.Current.Response.Buffer = true;
                    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
                    HttpContext.Current.Response.ContentType = "application/ms-excel";
                    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                    HttpContext.Current.Response.BinaryWrite(stream.ToArray());
                    HttpContext.Current.Response.Flush();
                    HttpContext.Current.Response.End();
                }
            }
        }
        #endregion

        #region 静态方法
        /// <summary>
        /// 将DataTable数据生成为Excel并输出至客户端。
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fileName"></param>
        public static void CreateXls(DataTable dataTable, string fileName, IDictionary<string, string> headers = null)
        {
            XlsWriter xls = new XlsWriter(dataTable);
            xls.SheetName = fileName.Substring(0, fileName.IndexOf("."));
            if (headers != null)
            {
                foreach (var item in headers)
                {
                    xls.SetHeaders(item.Key, item.Value);
                }
            }
            xls.Fill();
            xls.SaveAsXls(fileName);
        }
        #endregion
    }
}
